# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 2018-07-19
# Message: 通过指定车型的零件oe号(pid)从hengest网站上查询并获取到对应的汉格斯特厂家的零件数据以及汉格斯特厂家的零件可以对应那些品牌的车的这个零件
# Tips: 程序执行之前需要对headers头里的字段进行修改, 数据库需要进行相应配置, start_headers做其他准备,暂时无用
# Python Version: 2.7.1

import requests
import sys
import json
from collections import OrderedDict  # 引入有序字典
import time
import hashlib
import pymysql
import os
from lxml import etree

# 解决编码问题
reload(sys)
sys.setdefaultencoding('utf-8')


class Hengest_For_Pid(object):

    def __init__(self, db_config):
        # 存储html界面的路径
        self.base_path = '/Users/duanmengdi/Desktop/workspace/hengest_html/'
        self.start_url = "https://www.hengst.com/zh-hans/%E5%9C%A8%E7%BA%BF%E7%9B%AE%E5%BD%95/%E4%BA%A7%E5%93%81/3058110000-00/cn/"
        self.search_url = "https://www.hengst.com/zh-hans/%E5%9C%A8%E7%BA%BF%E7%9B%AE%E5%BD%95/%E6%90%9C%E7%B4%A2%E7%BB%93%E6%9E%9C/?searchType=freetext&catalog=cn&freetext={}"

        self.total_num = 0  # 用于数据量计数
        self.db_config = db_config

        # 初始请求的headers头
        self.start_headers = OrderedDict()
        self.start_headers['Cookie'] = '_ga=GA1.2.1153378978.1531383236; cookieconsent_status=dismiss; _gid=GA1.2.1487366910.1531967931; fe_typo_user=e977dc689ba91286fc34a9d81af215ae; _gat=1; be_typo_user=fc97c9d6b6b29601125a4279f87376e2'
        self.start_headers['Host'] = 'www.hengst.com'
        self.start_headers['Referer'] = 'https://www.hengst.com/zh-hans/%E5%9C%A8%E7%BA%BF%E7%9B%AE%E5%BD%95/'
        self.start_headers['User-Agent'] = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36'
        # self.start_headers[''] = ''

        # 搜索请求时的headers头
        self.search_headers = OrderedDict()
        self.search_headers['Cookie'] = '_ga=GA1.2.1153378978.1531383236; cookieconsent_status=dismiss; _gid=GA1.2.1487366910.1531967931; fe_typo_user=e977dc689ba91286fc34a9d81af215ae; _gat=1; be_typo_user=026e02ced470f9a56d2c539f3409b7ff'
        self.search_headers['Host'] = 'www.hengst.com'
        self.search_headers['Referer'] = 'https://www.hengst.com/zh-hans/%E5%9C%A8%E7%BA%BF%E7%9B%AE%E5%BD%95/'
        self.search_headers['User-Agent'] = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36'
        # self.start_headers[''] = ''

    def getmd5(self, str):
        md5 = hashlib.md5()
        md5.update(str)
        return md5.hexdigest()

    def get_conn(self, dbconfig_dict):
        conn = pymysql.connect(**dbconfig_dict)
        return conn

    def getcursor(self, conn, type='stream'):
        if type == 'stream':
            return conn.cursor(pymysql.cursors.SSCursor)  # 返回流式游标,查询大量数据时不占用内存(返回数据形式是元组)
        elif type == 'dict':
            return conn.cursor(pymysql.cursors.DictCursor)  # 返回字典形式游标,查询出的数据以字典形式返回
        else:
            raise Exception("cursor type error")

    def parse_url(self, url, method, post_data=None, headers=None):
        requests.packages.urllib3.disable_warnings()
        if method == 'GET':
            data = "GET" + url  # 拼接data
            md5 = self.getmd5(data)  # 获取此次请求电子指纹
            file_path = self.base_path + md5 + ".html"
            if os.path.exists(file_path):  # 判断文件是否存在,如果存在就从本地读取
                print "GET for local"
                with open(file_path, 'r') as f:
                    res = f.read()
                response = res
            else:  # 如果路径不存在, 从web端请求
                print "GET fro web"
                try:
                    response = requests.get(url=url, verify=False, headers=headers, timeout=30,
                                            allow_redirects=False)
                except Exception as e:
                    response = requests.get(url=url, verify=False, headers=headers, timeout=30,
                                            allow_redirects=False)

                if response:
                    with open(file_path, 'w') as f:  # 保存此次请求的html
                        f.write(response.content.decode('utf8'))
                    response = response.content.decode("utf8")
                else:
                    response = None

        elif method == "POST":
            data = "POST" + url + json.dumps(post_data)
            md5 = self.getmd5(data)
            file_path = self.base_path + md5 + ".html"
            if os.path.exists(file_path):  # 判断文件是否存在,如果存在就从本地读取
                print "POST for local"
                with open(file_path, 'r') as f:
                    res = f.read()
                response = res
            else:
                print "POST for web"
                try:
                    response = requests.post(url=url, data=post_data, verify=False, headers=headers, timeout=30,
                                             allow_redirects=False)
                except Exception as e:
                    response = requests.post(url=url, data=post_data, verify=False, headers=headers, timeout=30,
                                             allow_redirects=False)
                if response:

                    with open(file_path, 'w') as f:  # 保存此次请求的html
                        f.write(response.content.decode('utf8'))
                    response = response.content.decode("utf8")
                else:
                    response = None

        return response

    def save2db(self, data_dict, curs):
        pid = data_dict['pid'].decode('utf8')
        brand_name = data_dict['brand_name'].decode('utf8')
        part_brand = data_dict['part_brand'].decode('utf8')
        part_num = data_dict['part_num'].decode('utf8')
        part_name = data_dict['part_name'].decode('utf8')
        brand_name1 = data_dict['brand_name1'].decode('utf8')
        pid1 = data_dict['pid1'].decode('utf8')
        status = data_dict['status'].decode('utf8')
        sql = '''INSERT INTO `hengest` (`pid`, `brand_name`, `part_brand`, `part_num`, `part_name`, `brand_name1`, `pid1`, `status`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')''' % (pid, brand_name, part_brand, part_num, part_name, brand_name1, pid1, status)
        try:
            curs.execute(sql)
        except Exception as e:
            print e
            print sql
            exit()
        self.total_num += 1  # 数据总量计数

    def run(self):
        # 首先获取游标,从数据库读取pid出来
        conn1 = self.get_conn(dbconfig_dict=db_config)  # 获取数据源链接
        conn2 = self.get_conn(dbconfig_dict=db_config)  # 获取存储数据的链接
        curs1 = self.getcursor(conn=conn1)  # 获取数据源游标
        curs2 = self.getcursor(conn=conn2)  # 获取存储数据的游标
        sql = '''select brand,item,pid from 0716_pid'''  # 查询sql的语句
        curs1.execute(sql)
        for data in curs1:
            # time.sleep(1)  # 控制请求web的速度,防止被封,第二遍从本地请求的时候可以屏蔽
            local_pid = data[2]
            brand_name = data[0]

            # 获取完pid,开始请求初始页面,并获取搜索框
            # 直接拼接搜索请求url, get请求
            search_url = self.search_url.format(local_pid)
            search_result_response = self.parse_url(url=search_url, method="GET", headers=self.search_headers)

            if search_result_response:  # 如果有响应,生成etree对象, 没有响应则抛出异常
                search_result_html = etree.HTML(search_result_response)
            else:
                raise Exception("search_result_response is None type")
            result_tr_list = search_result_html.xpath(u'//table[@id="result-table"]/tbody/tr')
            if len(result_tr_list) <= 0:
                print local_pid, "没有搜索到数据 跳过"

            for result_tr in result_tr_list:
                detail_url = result_tr.xpath(u'.//a/@href')
                part_num = result_tr.xpath(u'.//a/text()')
                detail_url = detail_url[0] if len(detail_url)>0 else None
                part_num = part_num[0].replace(' ', '') if len(part_num)>0 else ""
                # 获取详情页url成功, 下一步请求该url

                # 请求详情页url
                detail_response = self.parse_url(url=detail_url, method="GET", headers=self.search_headers)

                if detail_response:
                    detail_html = etree.HTML(detail_response)
                else:
                    raise  Exception("detail_response is None type")

                # 详情页标题有零件名,获取一下
                part_name = detail_html.xpath(u'//div[@class="product__type"]/text()')
                part_name = part_name[0].replace(' ', '') if len(part_name)>0 else ""

                # 如果上一级没有获取到part_num, 详情页标题下边就是,再次尝试获取
                if part_num == "":
                    part_num = detail_html.xpath(u'//h1[@class="product__title"]/text()')
                    part_num = part_num[0].replace(' ', '') if len(part_num)>0 else ""

                # 获取最终数据列表
                # detail_tr_list = detail_html.xpath('//table[@id="result-table"]/tbody/tr')
                # detail_tr_list = detail_html.xpath(u'//th[text()="生产厂家原备件-/其他厂家号"]/../../../tbody/tr')
                detail_tr_list = detail_html.xpath(u'//th[@data-sort="manufacturer_name"]/../../../tbody/tr')

                for detail_tr in detail_tr_list:  # 获取最终数据, 清洗入库
                    brand_name1 = detail_tr.xpath(u'./td[2]/div/text()')
                    brand_name1 = brand_name1[0].strip().replace(' ', '') if len(brand_name1)>0 else ""

                    pid1 = detail_tr.xpath(u'./td[1]/div/text()')
                    pid1 = pid1[0].strip().replace(' ', '').replace('-', '') if len(pid1)>0 else ""

                    # 编写过滤数据条件, pid1等于local_pid的不要, 没有获取到pid1的不要
                    if pid1 == local_pid:
                        print "pid1=local_pid", pid1, local_pid
                        continue
                    elif pid1 == "":
                        continue

                    status = detail_tr.xpath(u'./td[4]/div/text()')
                    status = status[0].strip().replace(' ', '') if len(status)>0 else ""

                    # 组装最终数据字典
                    last_dict = dict(
                        pid=local_pid,
                        brand_name=brand_name,
                        part_brand='hengest',  # 爬的是hengest的网站,品牌当然是hengest
                        part_num=part_num.replace('\n', ''),
                        part_name=part_name,
                        brand_name1=brand_name1,
                        pid1=pid1,
                        status=status
                    )
                    print last_dict
                    self.save2db(data_dict=last_dict, curs=curs2)

                    # 判断sql插入数据缓存数量是否达到提交标准
                    if self.total_num % 100 == 0:
                        print '>>mysql提交<<'
                        conn2.commit()


        conn2.commit()  # 存储数据提交
        print self.total_num
        # 关闭sql链接,释放内存
        curs1.close()
        curs2.close()
        conn1.close()
        conn2.close()


if __name__ == '__main__':
    db_config = dict(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456",
        db="my_data",
        charset="utf8")

    tools = Hengest_For_Pid(db_config)
    tools.run()
    print "DONE GO HOME !!!"